package com.xinchuang.mapper.targetSystem;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xinchuang.entity.targetSystem.system.SpecialAttribute;
import com.xinchuang.entity.targetSystem.system.TargetType;
import com.xinchuang.vo.TypeIndexVo;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @ClassName TargetTypeMapper
 * @Description: TODO(目标类别数据层)
 * @Author zhangxuan
 * @Date 2021-03-31 15:38
 * @Verion V1.0
 */
public interface TargetTypeMapper extends BaseMapper<TargetType> {

    @Insert(value = {
            "INSERT INTO target_type ",
            "(type_id,parent_id,type_name,level,sort) ",
            "VALUES ",
            "(#{typeId},#{parentId},#{typeName},#{level},#{sort})"
    })
    int addType(TargetType targetType);

    @Insert(value = {
            "INSERT INTO sign_icon (id,icon) VALUES (#{id},#{icon})",
            "ON DUPLICATE KEY UPDATE id = #{id},icon = #{icon}"
    })
    int addSign(@Param("id") String id, @Param("icon") String name);

    @Insert(value = {
            "INSERT INTO target_type ",
            "(type_id,parent_id,type_name,level) ",
            "VALUES ",
            "(#{typeId},#{parentId},#{typeName},#{level})",
            "ON DUPLICATE KEY UPDATE parent_id = #{parentId}, type_name = #{typeName}, level = #{level}"
    })
    int addUpdateType(TargetType targetType);

    @Delete(value = "DELETE FROM target_type WHERE type_id = #{type_id}")
    int delType(@Param("type_id") String typeId);

    @Update(value = {
            "UPDATE target_type SET type_name = #{typeName} WHERE type_id = #{typeId}"
    })
    int updateType(TargetType targetType);

    @Select(value = "SELECT t.type_id,t.parent_id,t.type_name,t.level,s.icon FROM target_type t LEFT JOIN sign_icon s ON t.type_id = s.id ORDER BY sort ASC")
    @Results(id = "typeResultMap", value = {
            @Result(property = "typeId", column = "type_id"),
            @Result(property = "parentId", column = "parent_id"),
            @Result(property = "typeName", column = "type_name"),
            @Result(property = "level", column = "level"),
            @Result(property = "url", column = "icon")
    })
    List<TargetType> findAll();

    @Select(value = "SELECT type_id,parent_id,type_name,level FROM target_type WHERE parent_id = '1'")
    @ResultMap(value = "typeResultMap")
    List<TargetType> findAllByParentId();

    @Select(value = "SELECT type_name FROM target_type WHERE level = #{level}")
    List<String> findTargetNameByLevel(@Param("level") String level);

    @Select(value = "SELECT type_id,type_name FROM target_type WHERE level = #{level}")
    @ResultMap(value = "typeResultMap")
    List<TargetType> findTargetTypeByLevel(@Param("level") String level);

    @Select(value = "SELECT type_name FROM target_type WHERE level = #{level} AND parent_id = #{parentId}")
    List<String> findTargetNameByLevelAndParantId(@Param("level") String level, @Param("parentId") String parentId);

    @Select(value = "SELECT type_id,type_name FROM target_type WHERE parent_id = #{parentId} ORDER BY sort ASC")
    @ResultMap(value = "typeResultMap")
    List<TargetType> findTargetTypeByParantId(@Param("parentId") String parentId);

    @Select(value = "SELECT type_id,type_name FROM target_type WHERE level = #{level} AND parent_id = #{parentId}")
    @ResultMap(value = "typeResultMap")
    List<TargetType> findTargetTypeByLevelAndParantId(@Param("level") String level, @Param("parentId") String parentId);

    @Insert(value = {
            "INSERT INTO target_special_attribute ",
            "(id,type_id,key_name,key_value,key_type,show_type,required,info,data_range,verify,min,max,enumerator,row_value,message,sort) ",
            "VALUES ",
            "(#{id},#{typeId},#{keyName},#{key},#{keyType},#{showType},#{required},#{info},#{dataRange},#{verify},#{min},#{max},#{enumerator},#{row},#{message},#{sort})"
    })
    int addSpecial(SpecialAttribute specialAttribute);

    @Delete(value = "DELETE FROM target_special_attribute WHERE id = #{id}")
    int delSpecial(@Param("id") String id);

    @Update(value = {
            "<script>",
            "UPDATE target_special_attribute ",
            "<set>",
            "<if test = 'keyName != null'>key_name = #{keyName},</if>",
            "<if test = 'key != null'>key_value = #{key},</if>",
            "<if test = 'keyType != null'>key_type = #{keyType},</if>",
            "<if test = 'showType != null'>show_type = #{showType},</if>",
            "<if test = 'required != null'>required = #{required},</if>",
            "<if test = 'info != null'>info = #{info},</if>",
            "<if test = 'dataRange != null'>data_range = #{dataRange},</if>",
            "<if test = 'verify != null'>verify = #{verify},</if>",
            "<if test = 'min != null'>min = #{min},</if>",
            "<if test = 'max != null'>max = #{max},</if>",
            "<if test = 'enumerator != null'>enumerator = #{enumerator},</if>",
            "<if test = 'row != null'>row_value = #{row},</if>",
            "<if test = 'message != null'>message = #{message},</if>",
            "</set>",
            "WHERE id = #{id}",
            "</script>"
    })
    int updateSpecial(SpecialAttribute specialAttribute);

    @Select(value = {
            "SELECT id,key_name,key_value,key_type,show_type,required,info,data_range,verify,min,max,enumerator,row_value,message,sort ",
            "FROM target_special_attribute WHERE type_id = #{typeId} ORDER BY sort ASC"
    })
    @Results(id = "specialResultMap", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "typeId", column = "type_id"),
            @Result(property = "keyName", column = "key_name"),
            @Result(property = "key", column = "key_value"),
            @Result(property = "keyType", column = "key_type"),
            @Result(property = "showType", column = "show_type"),
            @Result(property = "required", column = "required"),
            @Result(property = "info", column = "info"),
            @Result(property = "dataRange", column = "data_range"),
            @Result(property = "verify", column = "verify"),
            @Result(property = "min", column = "min"),
            @Result(property = "max", column = "max"),
            @Result(property = "enumerator", column = "enumerator"),
            @Result(property = "row", column = "row_value"),
            @Result(property = "message", column = "message"),
            @Result(property = "sort", column = "sort")
    })
    List<SpecialAttribute> findSpecialByTypeId(@Param("typeId") String typeId);

    @Select(value = "SELECT id FROM target_special_attribute WHERE key_value = #{key} LIMIT 1")
    String selectIdByKey(@Param("key") String key);

    @Select(value = "SELECT id FROM target_special_attribute WHERE type_id = #{typeId} AND key_name = #{keyName} LIMIT 1")
    String selectIdByTypeIdAndKeyName(@Param("typeId")String typeId, @Param("keyName") String keyName);

    @Select(value = {
            "SELECT id,type_id,sort ",
            "FROM target_special_attribute WHERE id = #{id} LIMIT 1"
    })
    @ResultMap(value = "specialResultMap")
    SpecialAttribute findSpecialById(@Param("id") String id);

    @Select(value = {
            "SELECT id,sort FROM target_special_attribute LIMIT 1"
    })
    @ResultMap(value = "specialResultMap")
    SpecialAttribute findSpecialOne();

    @Select(value = "SELECT type_id FROM target_special_attribute GROUP BY type_id")
    List<String> findTypeIds();

    @Select(value = "SELECT MAX(sort) FROM target_type WHERE parent_id = #{parent_id}")
    Integer selectSpecialCount(@Param("parent_id") String parent_id);

    @Select(value = "SELECT * FROM target_type WHERE type_id = #{id}")
    @Results(value = {
            @Result(property = "typeId", column = "type_id"),
            @Result(property = "parentId", column = "parent_id"),
            @Result(property = "typeName", column = "type_name"),
            @Result(property = "level", column = "level"),
            @Result(property = "sort", column = "sort")
    })
    TargetType selectTypeOnly(@Param("id") String id);

    @Select("SELECT type_name FROM target_type WHERE type_id = #{id}")
    String getTypeName(@Param("id") String id);

    @Select("SELECT type_id FROM target_type WHERE type_name = #{name}")
    String getTypeId(@Param("name") String name);

    @Select(value = "SELECT a.type_id,a.sort FROM (SELECT type_id,sort FROM target_type WHERE parent_id = #{parentId} ORDER BY sort ASC) AS a JOIN (SELECT sort FROM target_type WHERE type_id = #{id}) AS b ON 1=1 WHERE a.sort < b.sort ORDER BY a.sort DESC LIMIT 1")
    @Results(value = {
            @Result(property = "typeId", column = "type_id"),
            @Result(property = "sort", column = "sort")
    })
    TargetType selectUpTargetType(@Param("id") String id, @Param("parentId") String parentId);

    @Select(value = "SELECT a.type_id,a.sort FROM (SELECT type_id,sort FROM target_type WHERE parent_id = #{parentId} ORDER BY sort ASC) AS a JOIN (SELECT sort FROM target_type WHERE type_id = #{id}) AS b ON 1=1 WHERE a.sort > b.sort ORDER BY a.sort ASC LIMIT 1")
    @Results(value = {
            @Result(property = "typeId", column = "type_id"),
            @Result(property = "sort", column = "sort")
    })
    TargetType selectDownTargetType(@Param("id") String id, @Param("parentId") String parentId);

    @Update("UPDATE target_type SET sort = #{sort} WHERE type_id = #{typeId}")
    int updateTypeSort(@Param("typeId") String id, @Param("sort") int sort);

    @Delete(value = {
            "DELETE FROM target_info WHERE target_id = #{targetId}",
    })
    int deleteInfoByTargetId(@Param("targetId") String targetId);

    @Select(value = {
            "SELECT id FROM target_special_attribute WHERE type_id = #{typeId} ORDER BY sort ASC"
    })
    List<String> findSpecialSort(@Param("typeId") String typeId);

    @Update(value = "UPDATE target_special_attribute SET sort = #{sort} WHERE id = #{id}")
    int updateSpecialSort(@Param("id") String id, @Param("sort") Integer sort);

    @Select("SELECT MAX(sort) FROM target_special_attribute WHERE type_id = #{typeId}")
    Integer getSpecialMaxSort(@Param("typeId") String typeId);

    @Select("SELECT id,sort AS value FROM target_special_attribute WHERE type_id = #{typeId} AND sort = (SELECT MIN(sort) FROM target_special_attribute WHERE type_id = #{typeId})")
    Map<String, Object> getMin(@Param("typeId") String typeId);

    @Select("SELECT id,sort AS value FROM target_special_attribute WHERE type_id = #{typeId} AND sort = (SELECT MAX(sort) FROM target_special_attribute WHERE type_id = #{typeId})")
    Map<String, Object> getMax(@Param("typeId") String typeId);

    @Select("SELECT id,sort FROM target_special_attribute WHERE sort < #{sort} AND type_id = #{typeId} ORDER BY sort DESC LIMIT 1")
    Map<String, Object> getUp(@Param("sort") int sort, @Param("typeId") String typeId);

    @Select("SELECT id,sort FROM target_special_attribute WHERE sort > #{sort} AND type_id = #{typeId} ORDER BY sort ASC LIMIT 1")
    Map<String, Object> getDown(@Param("sort") int sort, @Param("typeId") String typeId);

    @Select(value = "SELECT icon FROM sign_icon WHERE id = #{id}")
    String getSign(@Param("id") String id);

    @Select(value = {
            "SELECT tp.type_id AS id,IFNULL(si.icon,'default.png') AS icon FROM target_type tp LEFT JOIN sign_icon si ON tp.type_id = si.id"
    })
    @Results(value = {
            @Result(property = "id", column = "id", javaType = String.class),
            @Result(property = "icon", column = "icon", javaType = String.class)
    })
    List<Map<String, String>> getTypeSignAll();

    @Select(value = {
            "SELECT tp.type_id AS id,IFNULL(si.icon,'default.png') AS icon FROM posture_type tp LEFT JOIN sign_icon si ON tp.type_id = si.id"
    })
    @Results(value = {
            @Result(property = "id", column = "id", javaType = String.class),
            @Result(property = "icon", column = "icon", javaType = String.class)
    })
    List<Map<String, String>> getPostureTypeSignAll();

    @Select(value = {
            "SELECT type_id AS id,type_name AS surname FROM target_type"
    })
    @Results(value = {
            @Result(property = "id", column = "id", javaType = String.class),
            @Result(property = "surname", column = "surname", javaType = String.class)
    })
    List<Map<String, String>> getTypeNameAll();

    @Select(value = {
            "SELECT type_id AS id,type_name AS surname FROM target_sub_type"
    })
    @Results(value = {
            @Result(property = "id", column = "id", javaType = String.class),
            @Result(property = "surname", column = "surname", javaType = String.class)
    })
    List<Map<String, String>> getTypeSubNameAll();

    @Select("SELECT type_id AS id,sort FROM target_type WHERE `level` = #{level} AND sort = (SELECT MIN(sort) FROM target_type WHERE `level` = #{level}) LIMIT 1")
    Map<String, Object> getMinType(@Param("level") String level);

    @Select("SELECT type_id AS id,sort FROM target_type WHERE `level` = #{level} AND sort = (SELECT MAX(sort) FROM target_type WHERE `level` = #{level}) LIMIT 1")
    Map<String, Object> getMaxType(@Param("level") String level);

    @Select(value = {
            "SELECT a.type_name,a.index_value FROM (",
            "SELECT tt.type_name,IFNULL(SUM(tb.index_value),0) AS index_value FROM target_type tt",
            "JOIN target_base_data tb ON tt.type_id = tb.type",
            "JOIN systems_relation sr ON tb.target_id = sr.target_id",
            "WHERE sr.sys_id = #{sysId}",
            "GROUP BY tt.type_id) AS a ORDER BY a.index_value DESC"
    })
    @Results(value = {
            @Result(property = "typeName", column = "type_name"),
            @Result(property = "indexValue", column = "index_value")
    })
    List<TypeIndexVo> getSystemsTypeIndex(String sysId);

}
